1. Field of the Invention
The present invention relates generally to computer software, and, more particularly, to computer software for accessing, manipulating, and presenting data through a user interface.
2. Description of the Related Art
An important feature of any computer program, particularly end-user application programs, is its ability to interact with the user. How an application program allows a user to navigate, manipulate, retrieve, and display data is a critical component in the program's overall functionality. Often, the commercial success or failure of a software program can hinge on how easy it is for an end-user to actually use the software and, more specifically in some cases, how effectively and intelligently the data is displayed or presented to the user. More recently, while the expectations and sophistication of users has increased, users have disfavored programs in which repetitive data must be keyed into the program or in which obvious modifications to data are not made without prompting by a user. In short, as users' needs become more complex and as the users become more proficient with computer software, graphical and text based user interfaces should be easy to learn and be as intuitive or familiar as possible.
One general category of end-user application software, in which the user interface is an important component, addresses data storage, retrieval, and manipulation. One type of application within this category is often referred to as database management programs or simply database applications. Encompassed within this category are database systems referred to commercially as multidimensional databases or, in technical discussions, as Online Analytical Processing (OLAP) data stores. The OLAP paradigm is described in the white paper entitled "Providing OLAP (On-line Analytical Processing) to User-Analysts: An IT Mandate" by E. F. Codd, S. B. Codd, and C. T. Salley published by Codd & Date, Inc., and incorporated by reference herein for all purposes. Typically, a multidimensional database stores and organizes data in a way that better reflects how a user would want to view the data than is possible in a two-dimensional spreadsheet or relational database file. Multidimensional databases are better suited generally to handle applications with large volumes of numeric data and that require calculations on numeric data, such as business analysis and forecasting.
A dimension within multidimensional data is typically a basic categorical definition of data in a database outline (discussed in greater detail below). A multidimensional database can contain several dimensions thereby allowing analysis of a large volume of data from multiple viewpoints or perspectives. Thus, a dimension can also be described as a perspective or view of a specific dataset. A different view of the same data is referred to as an alternative dimension. A data management system that supports simultaneous, alternative views of datasets is said to be multidimensional. Using a business application as an example, dimensions are items such as TIME, ACCOUNTS, PRODUCT LINES, MARKETS, DIVISIONS, and so on. Within each dimension, there is typically a consolidation or other relationship between items.
A multidimensional database generally resides on a dedicated or shared server computer. The user interface resides on a front-end component, such as a client computer. With most multidimensional databases presently on the market, the format and functionality of the user interfaces are not all well-suited for complex business uses. Many of these user interfaces were not designed for performing high-volume numeric calculations or business analysis, the very attributes that make multidimensional databases inherently superior to other types of databases and data stores. Nor are they designed to allow easy and efficient navigation through data in a multidimensional database. In one sense the database and the user interface are too closely coupled and thus requires the user to learn a new interface and replace familiar interfaces with a foreign interface. An example of a multidimensional database with such a user interface is Express.TM. from Oracle Corporation of Foster City, Calif.
One type of user interface that improves on the basic, generally ill-suited interfaces accompanying multidimensional databases in recent years is one that utilizes widely recognized computer spreadsheet formats. A spreadsheet is used as a query tool to the multidimensional database. Many end-users using computers in a business environment and even those not in a business setting are more or less familiar with the concept of cells making up rows and columns in a spreadsheet format. Spreadsheet applications such as Excel.TM. from Microsoft Corporation of Redmond, Wash. and Lotus 1-2-3.TM. of IBM Corporation of Armonk, N.Y. are used widely in the business world and have been prevalent in the software market for nearly two decades. The user interface and format of such spreadsheet programs are very familiar to most business end-users, many of whom are potential users of multidimensional databases which store and organize the data displayed in the spreadsheet programs.
A multidimensional user interface utilizing a spreadsheet format is presently available from Hyperion Solutions Corporation of Sunnyvale, Calif. One feature of the spreadsheet user interface for multidimensional data navigation presently available is the ability to insert formulas in certain cells to express relationships among data in other cells. However, insertion of such formulas or formulaic relationships in one cell showing a particular relationship among values in other cells is confined only that one cell. Such formulaic relationships are not propagated or communicated to other portions of the multidimensional data as the data is retrieved from the database. Once a cell-based formulaic relationship is entered, it only applies to the cells explicitly entered by the user. However, it is likely that the user would want the same formulaic relationship to apply to cells holding the same type of value but having a different level of granularity. In a simple example, Table 1 shows two rows in a spreadsheet containing the following data:
TABLE 1 A B C D E 1. # Of Items Price Per Item Total Sales Sold 2. Entire Market 256 38 (B2*C2) 3.
The user inserts a formula in the last cell that provides the total sales amount for the entire country--data not available directly from the multidimensional database. Using the spreadsheet as a query tool, the user can click on, or zoom-in on, a market dimension (cell A2), causing the database to return members of the market dimension, such as, East, West, South, and Central. Presently, the total sales amount formula (B2*C2) is not propagated to each of the new rows even though the user would very likely want to see the total sales amount for each region, since he indicated that that data was desirable for national sales. The user has to key in the same formula in each cell after zooming in on the region dimension. This requires significant time typing in repetitive data and is error-prone.
Therefore, it would be desirable to have a user interface utilizing a spreadsheet that allows the user to create formulas in an ad hoc manner that uses multidimensional data and have those formulas propagate and replicate while the user is navigating through the multidimensional data. It would also be desirable to have the formulas propagate and replicate in an intuitive or intelligent manner and with reduced user intervention. Thus, there is a need for an automated and intelligent process for replicating user-entered formulas manipulate multidimensional data in a spreadsheet user interface.